package com.ecable.daoImpl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Vector;

import com.ecable.dao.AbstractDataAccessObject;
import com.ecable.daoI.PackageDaoI;
import com.ecable.util.LoggerManager;
import com.ecable.bean.PackagesTO;
import com.ecable.bean.ProfileTO;

public class PackagesDaoImpl extends AbstractDataAccessObject implements
		PackageDaoI {

	public Connection con;
	public PreparedStatement pstmt;

	public boolean addPackage(String pname, String pdiscription, int pamount) {
		boolean flag = false;
		try {
			con = getConnection();
			pstmt = con
					.prepareStatement("insert into packages values((select nvl(max(Packid),100)+1 from packages),?,?,?,'ACTIVE')");
			pstmt.setString(1, pname);
			pstmt.setString(2, pdiscription);
			pstmt.setInt(3, pamount);
			int i = pstmt.executeUpdate();
			if (i > 0) {
				flag = true;
			}

		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}

		return flag;
	}

	public Vector<PackagesTO> viewConnection(int userid) {
		Vector<PackagesTO> vpack = new Vector<PackagesTO>();
		PackagesTO pack = null;
		try {
			System.out.println(userid);
			con = getConnection();
			pstmt = con
					.prepareStatement("select connid,connplan from connection where userid=?");
			pstmt.setInt(1, userid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				pack = new PackagesTO();
				pack.setConnid(rs.getInt(1));
				System.out.println(rs.getInt(1));
				pack.setConnplan(rs.getString(2));
				System.out.println(rs.getString(2));
				vpack.add(pack);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
			System.out.println(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}

		return vpack;
	}

	public Vector<PackagesTO> viewPackage() {
		Vector<PackagesTO> vpack = new Vector<PackagesTO>();
		PackagesTO pack = null;
		try {
			con = getConnection();
			pstmt = con.prepareStatement("select * from packages");
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				pack = new PackagesTO();
				pack.setPackid(rs.getInt(1));
				System.out.println(rs.getInt(1));
				pack.setPackname(rs.getString(2));
				pack.setPackdiscription(rs.getString(3));
				pack.setPackamount(rs.getFloat(4));
				pack.setPackstatus(rs.getString(5));
				vpack.add(pack);
			}

		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}

		return vpack;
	}

	public Vector<PackagesTO> updateViewPackage(int packid) {
		Vector<PackagesTO> vpack = new Vector<PackagesTO>();
		PackagesTO pack = null;
		try {
			con = getConnection();
			pstmt = con
					.prepareStatement("select * from packages where packid=?");
			pstmt.setInt(1, packid);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				pack = new PackagesTO();
				pack.setPackid(rs.getInt(1));
				pack.setPackname(rs.getString(2));
				pack.setPackdiscription(rs.getString(3));
				pack.setPackamount(rs.getFloat(4));
				pack.setPackstatus(rs.getString(5));
				vpack.add(pack);
			}

		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}

		return vpack;
	}

	public boolean updatePackage(PackagesTO pack) {

		boolean flag = true;
		int packid = pack.getPackid();
		String pname = pack.getPackname();
		System.out.println(pname);
		String pdiscription = pack.getPackdiscription();
		System.out.println(pdiscription);
		float pamount = pack.getPackamount();
		System.out.println(pamount);
		String pstatus = pack.getPackstatus();
		System.out.println(pstatus);

		try {

			con = getConnection();
			pstmt = con
					.prepareStatement("update packages set packname=?, PACKDESCRIPTION  =?,packamount=?, packstatus=? where packid=?");

			pstmt.setString(1, pname);
			pstmt.setString(2, pdiscription);
			pstmt.setFloat(3, pamount);
			pstmt.setString(4, pstatus);
			pstmt.setInt(5, packid);
			int b = pstmt.executeUpdate();
			System.out.println(b);
			if (b > 0) {

				flag = true;
			} else {
				flag = false;
			}
		} catch (SQLException e) {
			System.out.println(e);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e);
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
				System.out.println(se);
			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e);
			}
		}
		return flag;
	}

	public Vector<PackagesTO> viewPackReg() {
		Vector<PackagesTO> vpack = new Vector<PackagesTO>();
		PackagesTO pack = null;
		try {
			con = getConnection();
			pstmt = con
					.prepareStatement("select packid,Packname from packages");
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				pack = new PackagesTO();
				pack.setPackid(rs.getInt(1));
				pack.setPackname(rs.getString(2));
				vpack.add(pack);
				System.out.println("At impl" + rs.getString(2));
			}

		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}

		return vpack;
	}

}
